package com.Spring.SpringSpms.dao;

/**SprintPlanningServiceImpl provides the implementation of the CRUD services**/
import java.util.List;
import java.util.ArrayList;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.dao.DataAccessException;

import com.Spring.SpringSpms.model.Sprint;

public class SprintPlanningDAOImpl implements SprintPlanningDAO
{
	private JdbcTemplate jdbcTemplate;

	public SprintPlanningDAOImpl(DataSource dataSource)
	{
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public JdbcTemplate getJdbcTemplate()
	{
		return jdbcTemplate;
	}

	//Sprint -> boolean
	//To save/Update the given Sprint into the database.
	//Assume Sprint having sprint_id = 0 as ,Sprint yet to persist.
	//Assume Sprint having sprint_id > 0 as ,Sprint to update.  
	@Override
	public void saveOrUpdate(Sprint sprintToAdd)
	{
		JdbcTemplate jdbct = getJdbcTemplate();

		if(sprintToAdd.getSprint_id() > 0)
		{
			String SPRINT_UPDATE_SQL = "UPDATE Sprint SET sprint_name=?, start_date=?, end_date=?,"+ 					"project_id=?, status_id=?";

			Object[] SPRINT_PROPERTIES = new Object[]
			{
				sprintToAdd.getSprint_name(),
				sprintToAdd.getStart_date(),
				sprintToAdd.getEnd_date(),
				sprintToAdd.getProject_id(),
				sprintToAdd.getStatus_id()
			};

			jdbct.update(SPRINT_UPDATE_SQL , SPRINT_PROPERTIES);
		}
		else
		{
			String SPRINT_ADD_SQL = "INSERT INTO Sprint(sprint_name, start_date, end_date,"+
				"project_id, status_id)"+"values(? , ? , ? , ? , ?)";

			Object[] SPRINT_PROPERTIES = new Object[]
			{
				sprintToAdd.getSprint_name(),
				sprintToAdd.getStart_date(),
				sprintToAdd.getEnd_date(),
				sprintToAdd.getProject_id(),
				sprintToAdd.getStatus_id()
			};
			
			jdbct.update(SPRINT_ADD_SQL , SPRINT_PROPERTIES);
		}		
	}

	@Override
	public void deleteSprint(int story_id)
	{
		String SPRINT_REMOVE_SQL = "DELETE from Sprint where sprint_id = ?";

		JdbcTemplate jdbct = getJdbcTemplate();
		
		jdbct.update(SPRINT_REMOVE_SQL , story_id);
	}

	@Override
	public Sprint findSprintById(int sprint_id)
	{
		String FIND_SPRINT = "SELECT * FROM Sprint where sprint_id = " + sprint_id;

		JdbcTemplate jdbct = getJdbcTemplate();

		Sprint sprint = jdbct.query(FIND_SPRINT , new ResultSetExtractor<Sprint>()
		{			
		
			@Override
			public Sprint extractData(ResultSet rs) throws SQLException , DataAccessException
			{
				if(rs.next())
				{
					Sprint sprint = new Sprint();

					sprint.setSprint_id(rs.getInt("sprint_id"));
					sprint.setSprint_name(rs.getString("sprint_name"));
					sprint.setStart_date(rs.getDate("start_date"));
					sprint.setEnd_date(rs.getDate("end_date"));
					sprint.setProject_id(rs.getInt("project_id"));
					sprint.setStatus_id(rs.getInt("status_id"));	
					
					return sprint; 
				}
				return null; 
			}
		});

		return sprint;
	}

	@Override
	public List<Sprint> listSprint()
	{
		String FIND_SPRINT_LIST = "SELECT * FROM Sprint";
		
		JdbcTemplate jdbct = getJdbcTemplate();
		List<Sprint> sprintList = jdbct.query(FIND_SPRINT_LIST , new RowMapper<Sprint>()
		{
			@Override
			public Sprint mapRow(ResultSet rs , int rowNum) throws SQLException
			{
				Sprint sprint = new Sprint();

				sprint.setSprint_id(rs.getInt("sprint_id"));
				sprint.setSprint_name(rs.getString("sprint_name"));
				sprint.setStart_date(rs.getDate("start_date"));
				sprint.setEnd_date(rs.getDate("end_date"));
				sprint.setProject_id(rs.getInt("project_id"));
				sprint.setStatus_id(rs.getInt("status_id"));	
				
				return sprint; 
			}
		});
		
		return sprintList;
	}
	
	public List<String> listSprintNames()
	{
		String sql = "SELECT sprint_name from Sprint";
		List<String> sprintList=jdbcTemplate.query(sql, new RowMapper<String> ()
				{
				@Override
				public String mapRow(ResultSet rs , int rowNum) throws SQLException
				{
					String sprint_name=new String();

					sprint_name=rs.getString("sprint_name");
					return sprint_name; 
				}
			});
				
		return sprintList;
	}
	
	public int findSprintbyName(String sprintName)
	{
		String sql="select sprint_id from Sprint where sprint_name=?";
		int sprint_id=jdbcTemplate.queryForInt(sql,sprintName);
		return sprint_id;
		
	}

	public void storyToSprint(String[] storyList , int sprintId)
	{
		String sql = "UPDATE Story SET sprint_id =? where story_id = ?";
		JdbcTemplate jdbct = getJdbcTemplate();

		for(int i = 0 ; i < storyList.length ; i++)
		{
			jdbct.update(sql , sprintId , Integer.parseInt(storyList[i]));
		}
	}

} 
